package com.xunlai.infra.permission.business.dao.repository;

import com.xunlai.infra.permission.business.dao.entity.RouteInfo;
import com.xunlai.infra.permission.storage.repository.BaseAllRoundRepository;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * @author liang
 * @date 8/2/23 4:12 PM
 */
@Transactional(rollbackFor = Exception.class)
public interface RouteInfoRepository extends BaseAllRoundRepository<RouteInfo,Long> {

    @Query("select distinct route_destination from travel_route_info where route_destination is not null ")
    List<String> selectDestinationList();

    @Query("""
        select * from travel_route_info where create_time between :start and :end 
        and if(IFNULL(:type,'') != '',route_type =:type,1=1 ) 
        and if(IFNULL(:template,'') != '',route_template=:template,1=1)
        and if(:routeNo != '',route_no like concat('%',:routeNo,'%'),1=1)
        and if(:destination != '',route_destination like concat('%',:destination,'%'),1=1)
        limit :pageStart,:pageSize
""")
    List<RouteInfo> selectAllByCreateTimeBetween(@Param("start") LocalDateTime start, @Param("end") LocalDateTime end, @Param("type") Long routeType,@Param("subject") Long routeSubject, @Param("template") Long routeTemplate, @Param("routeNo") String routeNo,@Param("destination") String routeDestination, @Param("pageStart") Integer pageStart, @Param("pageSize") Integer pageSize);

    @Query("""
        select count(*) from travel_route_info where create_time between :start and :end 
        and if(IFNULL(:type,'') != '',route_type =:type,1=1 ) 
        and if(IFNULL(:template,'') != '',route_template=:template,1=1)
        and if(:routeNo != '',route_no like concat('%',:routeNo,'%'),1=1)
        and if(:destination != '',route_destination like concat('%',:destination,'%'),1=1)
""")
    Long countByCreateTimeBetween(@Param("start") LocalDateTime start, @Param("end") LocalDateTime end, @Param("type") Long routeType,@Param("subject") Long routeSubject, @Param("template") Long routeTemplate,@Param("routeNo") String routeNo, @Param("destination") String routeDestination);

    @Query("""
        select * from travel_route_info where create_time between :start and :end 
        and if(IFNULL(:createUser,'') != '',create_user =:createUser,1=1 ) 
        and if(IFNULL(:type,'') != '',route_type =:type,1=1 ) 
        and if(IFNULL(:template,'') != '',route_template=:template,1=1)
        and if(:routeNo != '',route_no like concat('%',:routeNo,'%'),1=1)
        and if(:destination != '',route_destination like concat('%',:destination,'%'),1=1)
        limit :pageStart,:pageSize
""")
    List<RouteInfo> selectAllByCreateTimeBetween(@Param("start") LocalDateTime start, @Param("end") LocalDateTime end, @Param("type") Long routeType,@Param("subject") Long routeSubject, @Param("template") Long routeTemplate, @Param("routeNo") String routeNo,@Param("destination") String routeDestination,@Param("createUser") Long userId, @Param("pageStart") Integer pageStart, @Param("pageSize") Integer pageSize);

    @Query("""
        select count(*) from travel_route_info where create_time between :start and :end 
        and if(IFNULL(:createUser,'') != '',create_user =:createUser,1=1 ) 
        and if(IFNULL(:type,'') != '',route_type =:type,1=1 ) 
        and if(IFNULL(:template,'') != '',route_template=:template,1=1)
        and if(:routeNo != '',route_no like concat('%',:routeNo,'%'),1=1)
        and if(:destination != '',route_destination like concat('%',:destination,'%'),1=1)
""")
    Long countByCreateTimeBetween(@Param("start") LocalDateTime start, @Param("end") LocalDateTime end, @Param("type") Long routeType,@Param("subject") Long routeSubject, @Param("template") Long routeTemplate,@Param("routeNo") String routeNo, @Param("destination") String routeDestination,@Param("createUser") Long userId);
    Optional<RouteInfo> findByIdAndCreateUser(Long aLong,Long userId);


    @Query("""
        select count(info.id) from travel_route_info info 
        left join travel_ref_contact_route ref on ref.route_id = info.id 
        where ref.contact_id= :contactId
        and if(IFNULL(:template,'') != '',route_template=:template,1=1)
""")
    Long countByContactId(@Param("contactId") Long contactId,@Param("template") Long routeTemplate);

    @Query("""
        select info.* from travel_route_info info 
        left join travel_ref_contact_route ref on ref.route_id = info.id 
        where ref.contact_id= :contactId
        and if(IFNULL(:template,'') != '',route_template=:template,1=1)
        limit :pageStart,:pageSize
""")
    List<RouteInfo> selectPageByContactId(@Param("contactId") Long contactId,@Param("template") Long routeTemplate,@Param("pageStart") Integer pageStart, @Param("pageSize") Integer pageSize);
}
